package com.bs.areport.service.report;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.bs.areport.service.util.DataUtil;
import com.bs.areport.service.util.EntityType;
import com.bs.areport.service.util.MethodUtil;
import com.bs.areport.sys.Tools;
import com.bs.restframework.context.util.SystemContextUtil;
import com.bs.restframework.db.Database;
/**
 * 
 * @ClassName   类名：DeptIncomeByChargeDao 
 * @Description 功能说明：单位收费情况表服务类
 * TODO
 ************************************************************************
 * @date        创建日期：2012-03-08
 * @author      创建人：李新宇
 * @version     版本号：V1.1.0.0
 ***************************修订记录*************************************
 * 
 *   2012-03-08  李新宇   创建类
 *   2012-08-25      陈金滨   修改日期查询条件错误	
 *   2012-08-27  陈金滨   修改起始终止日期为第一天最后一天时查询没有联查月结表
 *   
 ***********************************************************************
 */
public class DeptIncomeByChargeDao {
	
	/*******************************************	  
	 * <p>函数名称：getCharges</p>
	 * <p>功能说明：获取项目列表/p>
	 * <p>参数说明：valueMap</p>
	 * <p>创建时间：2012-03-13</p>
	 * <p>作者：李新宇</p>
	 * *****************************************
	 */
	public List<Map> getCharges(Map<String, Object> valueMap) throws Exception
	{	
		//获取项目编码和项目级数
		String chargeCode=(String)valueMap.get("chargeCode");
		Integer chargeLevel=0;
		if (valueMap.get("chargeLevel")!=null)
		  chargeLevel=Integer.valueOf((String)valueMap.get("chargeLevel"));
		
		Database db = SystemContextUtil.getDatabase(this);
		List<Map> chargeList=null;
			
		String sql="SELECT F_CODE,F_NAME,F_LEVEL,F_ISLEAF FROM "+EntityType.ARP_BSC_CHARGE_TABLE+" WHERE 1=1";
		if(chargeCode!=null&&!"".equals(chargeCode)){
		    sql+=" AND　F_CODE LIKE '"+chargeCode+"%' and F_LEVEL="+String.valueOf((chargeLevel));
		}else {
			sql+=" AND F_LEVEL=1";
	    }
		sql+=" ORDER BY F_CODE";
		chargeList=db.queryList(Map.class, sql, null);
		    
		
		return chargeList;
	}
	
	/****************************************************** 
	 * <p>函数名称：getReport</p>
	 * <p>功能说明：获取报表数据</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ;/p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 * ****************************************************
	 */
	public Map<String, Object> getReportPro(Map<String, Object> valueMap,List<Map> colList,String userDeptCode) throws Exception
    {
		Database db = SystemContextUtil.getDatabase(this);
		Map<String, Object> resultMap=new HashMap<String, Object>();
		
		//单位条件
		String deptCondition="";
		//项目条件
		String chrgCondition="";
		//日期条件
		String dateCondition="";
		//月份条件
		String monCondition="";
		//级长
		String chargeJc=MethodUtil.getCharghJC(db);
		String deptJc=MethodUtil.getDeptJC(db);
		//获取参数
		int page = Integer.valueOf((String)valueMap.get("page"));
		int rows = Integer.valueOf((String)valueMap.get("rows"));
		String chargeCode=(String)valueMap.get("chargeCode");
		String isQuery=(String)valueMap.get("isQuery");
		String dateStart=(String)valueMap.get("dateStart");
		String dateEnd=(String)valueMap.get("dateEnd");
		
		//月份参数
		String monStart="";
		String monEnd="";
		//日期参数
		String bgnDate_1="";
		String bgnDate_2="";
		String endDate_1="";
		String endDate_2="";

		SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
		//如果起始和终止都为空，则设置默认的起始和终止日期
		if((dateStart==null||"".equals(dateStart))&&(dateEnd==null||"".equals(dateEnd))){
			Date date=new Date();
			//获取当前日期
			String datetime=df.format(date);
			dateStart=datetime.substring(0, datetime.length()-2)+"01";
			dateEnd=datetime;
		}
		
		//把查询日期整为8位
		dateStart=dateStart.replace("-", "");
		dateEnd=dateEnd.replace("-", "");	
		
		//生成查询时间
		SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMdd");
		
		//转换为日期类型
		Date bgnDate=sdf.parse(dateStart);
		Date endDate=sdf.parse(dateEnd);
		Calendar   calendar   =   Calendar.getInstance();
		//判断时间是否同一月份内  2012-08-25 陈金滨 
		boolean ifSameMon = false ;
		String bgnDateMon = dateStart.substring(0, 6).toString();
		String endDateMon = dateEnd.substring(0, 6).toString();		
		if(bgnDateMon.equals(endDateMon))
			ifSameMon = true ;
		//如果起始日期是月份的第一天，则把当月加入月份查询条件
		//否则把下月加入月份查询条件，把本月日期加入日期查询条件
		if("01".equals(dateStart.substring(6, 8))){
			monStart=dateStart.substring(0,6);
		}else{
			calendar.setTime(bgnDate);
			calendar.add(Calendar.MONTH, 1);
			calendar.set(calendar.DATE, 1);
			monStart=sdf.format(calendar.getTime()).substring(0,6);
			calendar.add(Calendar.DATE, -1);//日期回滚一天，也就是本月最后一天 
			bgnDate_1=dateStart;
			endDate_1=sdf.format(calendar.getTime());;
		}
		
		calendar.setTime(endDate);
		calendar.add(Calendar.MONTH,1);//加一个月  
		calendar.set(Calendar.DATE, 1);//把日期设置为当月第一天   
		calendar.add(Calendar.DATE, -1);//日期回滚一天，也就是本月最后一天 
		//如果终止日期是月份的最后一天，则把本月加入月份条件
		//否则把上月加入月份查询条件，把日期作为日期查询条件
		if(dateEnd.equals(sdf.format(calendar.getTime()))){
			monEnd=dateEnd.substring(0,6);
		}else{
			calendar.roll(Calendar.MONTH,-1);
			monEnd=sdf.format(calendar.getTime()).substring(0,6);
			bgnDate_2=dateEnd.substring(0,6)+"01";
			endDate_2=dateEnd;
		}
		//2012-08-25    陈金滨   修改日期查询条件错误
        //如果起始日期和终止日期在同一月份内，则不将月份加入月份查询条件
        if(ifSameMon)
		{
			monStart="";
			monEnd= "";
		}	
      //如果起始日期和终止日期在同一月份内，并且日期包含这个月份，即日期从1号开始到最后一天，则将月份加入月份查询条件
		if(ifSameMon&&"01".equals(dateStart.substring(6, 8))&&dateEnd.equals(sdf.format(calendar.getTime())))
		{
			monStart=dateStart.substring(0,6);
			monEnd=dateEnd.substring(0,6);
		}

	
		Integer chargeLevel=1;
		
		//如果项目级数不为空则设置项目级数
		if(valueMap.get("chargeLevel")!=null&&Integer.valueOf((String)valueMap.get("chargeLevel"))>0)
		    chargeLevel=Integer.valueOf((String)valueMap.get("chargeLevel"));
		
		String deptCode=(String)valueMap.get("deptCode");
		
		Integer deptLevel=1;
		
        //如果单位级数为空则设置单位级数	
		if(valueMap.get("deptLevel")!=null&&Integer.valueOf((String)valueMap.get("deptLevel"))>0)
		    deptLevel=Integer.valueOf((String)valueMap.get("deptLevel"));
		
		//如果是查询访问，则根据查询单位的编码设定单位级数(转取和查询不同)
		if(isQuery!=null&&!"".equals(isQuery)&&deptCode!=null&&!"".equals(deptCode)){
			deptLevel=DataUtil.getLevel(deptJc, deptCode);
		}
		
		//如果用户所属单位级别低于查询单位级别，则改变单位级数条件
		if(userDeptCode!=null&&!"".equals(userDeptCode)){
			Integer dlevel=DataUtil.getLevel(deptJc, userDeptCode);
		    if(dlevel>deptLevel) 
		    	deptLevel=dlevel; 
		}
		
		//计算查询编码的长度
		String chargeCodeLenght=getCodeLenght(chargeJc,chargeLevel);
		String deptCodeLenght=getCodeLenght(deptJc, deptLevel);

		//成查询条件
		  if(isQuery!=null&&!"".equals(isQuery)){
				chrgCondition+=" AND C.F_LEVEL>="+String.valueOf((chargeLevel));
				if (deptCode!=null&&!"".equals(deptCode))
					deptCondition+=" AND D.F_CODE LIKE'"+deptCode+"%' AND D.F_LEVEL>="+String.valueOf((deptLevel));

			}else{
				chrgCondition+=" AND C.F_LEVEL>="+String.valueOf((chargeLevel));
				deptCondition+=" AND D.F_LEVEL>="+String.valueOf((deptLevel));
				if (chargeCode!=null&&!"".equals(chargeCode))
					chrgCondition+=" AND C.F_CODE LIKE '"+chargeCode+"%'";
				if (deptCode!=null&&!"".equals(deptCode))
					deptCondition+=" AND D.F_CODE LIKE '"+deptCode+"%' ";
			}
		  
		  //如果可以按月查询，分开查询条件类型
		  if(!"".equals(bgnDate_1)&&!"".equals(endDate_1)&&!"".equals(bgnDate_2)&&!"".equals(endDate_2)&&!"".equals(monStart)&&!"".equals(monEnd)){			  
			   dateCondition+=" AND ((F_DATE>="+bgnDate_1+" AND F_DATE<="+endDate_1+") OR (F_DATE>="+bgnDate_2+" AND F_DATE<="+endDate_2+"))";
			   monCondition+=" AND F_MON >="+monStart+" AND F_MON <="+monEnd;
		  }
		  /**
		   * 2012-08-27 陈金滨 修改起始终止日期为第一天最后一天时查询没有联查月结表		   *
		   **/
		  //起始日期为月份第一天
		  else if("".equals(bgnDate_1)&&"".equals(endDate_1)&&!"".equals(bgnDate_2)&&!"".equals(endDate_2)&&!"".equals(monStart)&&!"".equals(monEnd))  
		  {
			   dateCondition+=" AND (F_DATE>="+bgnDate_2+" AND F_DATE<="+endDate_2+")";
			   monCondition+=" AND F_MON >="+monStart+" AND F_MON <="+monEnd;
		  }
		  //终止日期为月份最后一天
		  else if("".equals(bgnDate_2)&&"".equals(endDate_2)&&!"".equals(bgnDate_1)&&!"".equals(endDate_1)&&!"".equals(monStart)&&!"".equals(monEnd))
		  {
			   dateCondition+=" AND (F_DATE>="+bgnDate_1+" AND F_DATE<="+endDate_1+")";
			   monCondition+=" AND F_MON >="+monStart+" AND F_MON <="+monEnd;
		  }
		  //起始日期为当月第一天，并且终止日期为月份最后一天
		  else if("".equals(bgnDate_1)&&"".equals(endDate_1)&&"".equals(bgnDate_2)&&"".equals(endDate_2)&&!"".equals(monStart)&&!"".equals(monEnd))
		  {
			  dateCondition+=" AND 1=2 ";
			  monCondition+=" AND F_MON >="+monStart+" AND F_MON <="+monEnd;
		  }
		  //日期条件不跨月，并且起始日期，终止日期都不是月份第一天和最后一天
		  else{ 
		      dateCondition+=" AND F_DATE>="+dateStart;
		      dateCondition+=" AND F_DATE<="+dateEnd;	 
		  }
		  
		  //用户所属单位条件
		  if(userDeptCode!=null&&!"".equals(userDeptCode))
		      deptCondition+=" AND D.F_CODE LIKE '"+userDeptCode+"%'";                 

		//获取所有有数据的单位
		SimpleDateFormat dfs = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
		List<Map> deptList = getUserListCountPro(db,deptCondition,deptCodeLenght);
		int count=deptList.size();
		
		//根据分页条件拼接出查询条件的单位范围
		String deptString="";
		Map<String,Object> deptsMap=new HashMap<String, Object>();
		int bgnIndex=(page-1)*rows;
		int endIndex=bgnIndex+rows;
		if((count>=bgnIndex)&&(count>0)){
			if(count<endIndex)
				endIndex=count;
			for(int i=bgnIndex;i<endIndex;i++){
				deptString+="'"+((Map)deptList.get(i)).get("DEPTCODE")+"',";
				deptsMap.put((String)((Map)deptList.get(i)).get("DEPTCODE"), null);
			}
			deptString=deptString.substring(0, deptString.length()-1);
		}
		
		//日结表查询语句
		String sql=" SELECT substr(D.F_CODE, 0, "+deptCodeLenght+") AS DEPTCODE,substr(C.F_CODE, 0, "+chargeCodeLenght+") AS CHARGECODE,SUM(R.F_AMT) AS AMT";
        sql+=" FROM "+EntityType.ARP_BUS_CHRGDAY_TABLE+" R ";
        sql+=" LEFT JOIN "+EntityType.ARP_BSC_DEPT_TABLE+" D ON D.F_ID = R.F_DEPTID ";
        sql+=" LEFT JOIN "+EntityType.ARP_BSC_CHARGE_TABLE+" C ON C.F_ID = R.F_CHRGID ";
        sql+=" WHERE 1=1 ";                         
		sql+=chrgCondition;
		sql+=deptCondition;
		sql+=dateCondition;
		if("".equals(deptString)){
			sql+="AND 1<>1";
		}else{
			
			sql+="AND substr(D.F_CODE, 0, "+deptCodeLenght+") IN ("+deptString+")";
		}
		sql+=" GROUP BY substr(D.F_CODE, 0, "+deptCodeLenght+"),substr(C.F_CODE, 0, "+chargeCodeLenght+")";
		
		 //如果可以按月查询，联查月结表
		if(!"".equals(monStart)&&!"".equals(monCondition)){
			sql+=" UNION ALL";
			sql+=" SELECT substr(D.F_CODE, 0, "+deptCodeLenght+") AS DEPTCODE,substr(C.F_CODE, 0, "+chargeCodeLenght+") AS CHARGECODE,SUM(R.F_AMT) AS AMT";
	        sql+=" FROM "+EntityType.ARP_BUS_CHRGMON_TABLE+" R ";
	        sql+=" LEFT JOIN "+EntityType.ARP_BSC_DEPT_TABLE+" D ON D.F_ID = R.F_DEPTID ";
	        sql+=" LEFT JOIN "+EntityType.ARP_BSC_CHARGE_TABLE+" C ON C.F_ID = R.F_CHRGID ";
	        sql+=" WHERE 1=1 ";                         
			sql+=chrgCondition;
			sql+=deptCondition;
			sql+=monCondition;
			if("".equals(deptString)){
				sql+="AND 1<>1";
			}else{
				
				sql+="AND substr(D.F_CODE, 0, "+deptCodeLenght+") IN ("+deptString+")";
			}
			sql+=" GROUP BY substr(D.F_CODE, 0, "+deptCodeLenght+"),substr(C.F_CODE, 0, "+chargeCodeLenght+")";
		}
        
		//行列转制生成最终SQL
		sql=getSQL(colList,sql); 
		
		List<Map> listMap =db.queryList(Map.class, sql,null);
		
		List<Map> foots=new ArrayList<Map>();
		List<Map> ftr=new ArrayList<Map>();
		Map<String, Object> footer=new HashMap<String, Object>();
		footer.put("DEPTNAME", "合计");
		
		if(listMap.size()>0){
		//合计
		for (Map map : listMap) {
			for (Map m : colList) {
				if(footer.get(m.get("F_CODE"))==null)
					footer.put((String)m.get("F_CODE"), new Double(0));
				Double fDouble=(Double)footer.get(m.get("F_CODE"));
				Double sDouble=Double.parseDouble(map.get(m.get("F_CODE")).toString());
				Double sumDouble=fDouble+sDouble;
				footer.put((String)m.get("F_CODE"),sumDouble );
						        
			}
		}
		}else {
			for (Map m : colList) {
				footer.put((String)m.get("F_CODE"), new Double(0));
		    }
	    }
		for (Map m : colList) {
			if(Double.parseDouble(footer.get(m.get("F_CODE")).toString())==0){
			Map<String, Object> f=new HashMap<String, Object>();
			f.put("field",m.get("F_CODE"));
			ftr.add(f);
			}
		}
		
		
		foots.add(footer);
		resultMap.put("ftr", ftr);
		resultMap.put("rows", listMap);
		resultMap.put("total", count);
		resultMap.put("footer",foots);
		resultMap.put("deptCode", deptCode); 
		resultMap.put("chargeCode", chargeCode);
		resultMap.put("chargeLevel", chargeLevel);
		resultMap.put("deptLevel", deptLevel); 
		
		//置空条件
		monStart="";
		monEnd="";
		bgnDate_1="";
		bgnDate_2="";
		endDate_1="";
		endDate_2="";
		deptCondition="";
		chrgCondition="";
		dateCondition="";
		monCondition="";
		return resultMap;
	}
	
	
	/*******************************************************************************	  
	 * <p>函数名称：getUserListCount</p>
	 * <p>功能说明：获取表记录数</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 * *****************************************************************************
	 */
	public static List<Map> getUserListCountPro(Database db,String condition,String deptLen)throws Exception {
		
		String sql=" SELECT distinct substr(D.F_CODE, 0, "+deptLen+") AS DEPTCODE FROM "+EntityType.ARP_BUS_CHRGDAY_TABLE+" R ";
		sql+=" LEFT JOIN "+EntityType.ARP_BSC_DEPT_TABLE+" D   ON D.F_ID = R.F_DEPTID ";
		sql+=" WHERE 1=1 ";
		sql+=condition;
		sql+=" ORDER BY substr(D.F_CODE, 0, "+deptLen+")";
		List<Map> list =db.queryList(Map.class, sql,null);
		
		return list;
	}	
	
	/*************************************************************************
	 * <p>函数名称：getColString</p>
	 * <p>功能说明：根据要查询的项目，生成查询列</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 * ***********************************************************************
	 */
	public static String getSQL(List<Map> chargeList,String sqlString){ 
		String colString="";
		String chargeCode="";
		String chargeName="";
		String sql="";

		for (Map map : chargeList) {
			chargeCode=(String)map.get("F_CODE");
			chargeName=(String)map.get("F_NAME");
			colString+=",sum(decode(CHARGECODE,"+chargeCode+", AMT,0)) \""+chargeCode+"\"";
		}

		sql="SELECT Z.DEPTCODE,M.F_NAME AS DEPTNAME,M.F_ISLEAF AS DEPTLEAF  "+colString+" FROM (";
		sql+=sqlString;
		sql+=") Z LEFT JOIN "+EntityType.ARP_BSC_DEPT_TABLE+" M ON M.F_CODE=Z.DEPTCODE ";
		sql+=" WHERE Z.DEPTCODE IS NOT NULL ";
		sql+=" GROUP BY Z.DEPTCODE,M.F_NAME,M.F_ISLEAF ";
		sql+=" ORDER BY Z.DEPTCODE ";
		return sql;
	}
	
	/*************************************************************************
	 * 
	 * <p>函数名称：getColString</p>
	 * <p>功能说明：根据要查询的项目，生成查询列</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 * ***********************************************************************
	 */
	public static String getCodeLenght(String jc,Integer level){
        Integer len=0;
		
        for(int i=0;i<level;i++){
        	len=len+Integer.parseInt(jc.substring(i, i+1));
        }
        
		return String.valueOf(len);
	}
	
	/*************************************************************************
	 * <p>函数名称：getQtip</p>
	 * <p>功能说明：获取图表数据/p>
	 * <p>参数说明：</p>
	 * <p>创建时间：2012-03-13</p>
	 * <p>作者：李新宇</p>
	 * ***********************************************************************
	 */
	public List<Map> getQtip(Map<String, Object> valueMap,String userDeptCode) throws Exception
	{
		Database db = SystemContextUtil.getDatabase(this);
		Map<String, Object> resultMap=new HashMap<String, Object>();
		
		//级长
		String chargeJc=MethodUtil.getCharghJC(db);
		String deptJc=MethodUtil.getDeptJC(db);
		
		String dateStart=(String)valueMap.get("dateStart");
		String dateEnd=(String)valueMap.get("dateEnd");
		String deptCode=(String)valueMap.get("deptCode");
		Integer deptLevel=1;
		if(valueMap.get("deptLevel")!=null&&Integer.valueOf((String)valueMap.get("deptLevel"))>0)
		    deptLevel=Integer.valueOf((String)valueMap.get("deptLevel"));
		String chargeCode=(String)valueMap.get("chargeCode");
		Integer chargeLevel=1;
		if(valueMap.get("chargeLevel")!=null&&Integer.valueOf((String)valueMap.get("chargeLevel"))>0)
		    chargeLevel=Integer.valueOf((String)valueMap.get("chargeLevel"));
		String groupType=(String)valueMap.get("groupType");
		
		String sql="";
			
		//以项目做百分比
		if("dept".equals(groupType))
		{
			String chargeCodeLenght=getCodeLenght(chargeJc,chargeLevel);
			sql="SELECT Z.CODE, M.F_NAME AS \"label\", SUM(Z.AMT) AS \"value\" FROM (";
			sql+="SELECT substr(O.F_CODE,0,"+chargeCodeLenght+") AS CODE,R.F_AMT AS AMT";
			sql+=" FROM "+ EntityType.ARP_BUS_CHRGDAY_TABLE+" R";
			sql+=" LEFT JOIN "+EntityType.ARP_BSC_CHARGE_TABLE+" O ON O.F_ID=R.F_CHRGID ";
			sql+=" LEFT JOIN "+EntityType.ARP_BSC_DEPT_TABLE+" D ON D.F_ID=R.F_DEPTID ";
			sql+=" WHERE O.F_LEVEL>='"+chargeLevel+"'";
			if(userDeptCode!=null&&!"".equals(userDeptCode)){
				sql+=" AND D.F_CODE LIKE '"+userDeptCode+"%'";	
			}
			if(deptCode!=null&&!"".equals(deptCode)){
				sql+=" AND D.F_CODE LIKE '"+deptCode+"%'";			
			}
			if(chargeCode!=null&&!"".equals(chargeCode)){
				sql+=" AND O.F_CODE LIKE '"+chargeCode+"%'";
			}
			if (dateStart!=null&&!"".equals(dateStart))
				sql+=" AND R.F_DATE>="+dateStart.replace("-", "");
			if (dateEnd!=null&&!"".equals(dateEnd))
				sql+=" AND R.F_DATE<="+dateEnd.replace("-", "");
			sql+=") Z ";
			sql+=" LEFT JOIN "+EntityType.ARP_BSC_CHARGE_TABLE+" M ON M.F_CODE=Z.CODE ";
			sql+=" GROUP BY M.F_NAME,Z.CODE ORDER BY to_number(Z.CODE) ";
				
		}
		else if("charge".equals(groupType))//以单位做百分比
		{	
			String deptCodeLenght=getCodeLenght(deptJc, deptLevel);
			sql="SELECT Z.CODE, M.F_NAME AS \"label\", SUM(Z.AMT) AS \"value\" FROM (";
			sql+="SELECT substr(O.F_CODE,0,"+deptCodeLenght+") AS CODE,R.F_AMT AS AMT";
			sql+=" FROM "+ EntityType.ARP_BUS_CHRGDAY_TABLE+" R";
			sql+=" LEFT JOIN "+EntityType.ARP_BSC_DEPT_TABLE+" O ON O.F_ID=R.F_DEPTID ";
			sql+=" LEFT JOIN "+EntityType.ARP_BSC_CHARGE_TABLE+" C ON C.F_ID=R.F_CHRGID ";
			sql+=" WHERE O.F_LEVEL>='"+deptLevel+"'";
			if(chargeCode!=null&&!"".equals(chargeCode)){
				sql+=" AND C.F_CODE LIKE '"+chargeCode+"%'";			
			}
			if(userDeptCode!=null&&!"".equals(userDeptCode)){
				sql+=" AND O.F_CODE LIKE '"+userDeptCode+"%'";	
			}
			if(deptCode!=null&&!"".equals(deptCode)){
				sql+=" AND O.F_CODE LIKE '"+deptCode+"%'";			
			}
			if (dateStart!=null&&!"".equals(dateStart))
				sql+=" AND R.F_DATE>="+dateStart.replace("-", "");
			if (dateEnd!=null&&!"".equals(dateEnd))
				sql+=" AND R.F_DATE<="+dateEnd.replace("-", "");
			sql+=") Z ";
			sql+=" LEFT JOIN "+EntityType.ARP_BSC_DEPT_TABLE+" M ON M.F_CODE=Z.CODE ";
			sql+=" GROUP BY M.F_NAME,Z.CODE ORDER BY to_number(Z.CODE) ";
		}
		
		List<Map> list=db.queryList(Map.class, sql, null);
		
		return list;
	}
}
